package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;

import javax.swing.DefaultListModel;


import entidades.Disco;
import entidades.Grupo;
import entidades.Tema;

public class MediaDAO {
	
	public static DefaultListModel obtenerListaTemas(String busqueda, Hashtable<Integer, Tema> temaId, int caso) throws SQLException {
		
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		Tema tema;
		String sql = "";
		
		switch (caso) {
		case 1:
			sql =	" SELECT T.idGrupo, T.idDisco, T.url, T.nombreTema, T.id, D.nombreDisco, G.nombreGrupo " + 
					" FROM temas as T, discos as D, grupos as G " + 
					" WHERE T.idGrupo = G.id AND " +
					" 		T.idDisco = D.id " +	
					"LIMIT 0,20";
			break;
			
		case 2:
			sql = 	" SELECT T.idGrupo, T.idDisco, T.url, T.nombreTema, T.id, D.nombreDisco, G.nombreGrupo " + 
					" FROM temas as T, discos as D, grupos as G " + 
					" WHERE T.idGrupo = G.id AND " +
					" 		T.idDisco = D.id AND " +
					" 		T.nombreTema LIKE '%" + busqueda + "%' " +
					"LIMIT 0,20";
			break;
		}
		
		//System.out.println(sql);
		ResultSet rs = s.executeQuery(sql);
		DefaultListModel listModel = new DefaultListModel();
		
		temaId.clear();
		int i = 0;
		
		while(rs.next()){
			tema = new Tema(rs.getInt("idGrupo"), rs.getInt("idDisco"), rs.getString("url"), rs.getString("nombreTema"), rs.getInt("id"), rs.getString("nombreGrupo"), rs.getString("nombreDisco"));
			temaId.put(i, tema);
			listModel.addElement(tema.getTitle() + " - " + tema.getNombreDisco() + " - " + tema.getNombreGrupo());
			i++;
		}
		
		rs.close ();
		conn.close();
		return listModel;
	}
	
	public static DefaultListModel obtenerListaDiscos(String busqueda, Hashtable<Integer, Disco> discoId, int caso) throws SQLException {
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		Disco disco;
		String sql = "";
		
		switch (caso) {
		case 1:
			sql =	"SELECT D.id, D.nombreDisco, D.idGrupo, G.nombreGrupo " +
					" FROM discos as D, grupos as G " +
					" WHERE D.idGrupo = G.id " +
					" LIMIT 0,20";
			break;
			
		case 2:
			sql = 	"SELECT D.id, D.nombreDisco, D.idGrupo, G.nombreGrupo " +
					" FROM discos as D, grupos as G " +
					" WHERE D.idGrupo = G.id AND " +
					" 		D.nombreDisco LIKE '%" + busqueda + "%' " +	
					" LIMIT 0,20";
			break;
		}
		
		//System.out.println(sql);
		ResultSet rs = s.executeQuery(sql);
		DefaultListModel listModel = new DefaultListModel();
		
		discoId.clear();
		int i = 0;
		
		while(rs.next()){
			disco = new Disco(rs.getInt("id"), rs.getString("nombreDisco"), rs.getInt("idGrupo"), rs.getString("nombreGrupo"));
			discoId.put(i, disco);
			listModel.addElement(disco.getTitle() + " - " + disco.getNombreGrupo());
			i++;
		}
		
		rs.close ();
		conn.close();
		return listModel;
	}

	public static DefaultListModel obtenerListaGrupos(String busqueda, Hashtable<Integer, Grupo> grupoId, int caso) throws SQLException {
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		Grupo grupo;
		String sql = "";
		
		switch (caso) {
		case 1:
			sql =	"SELECT id, nombreGrupo " +
					" FROM grupos " +
					" LIMIT 0,20";
			break;
			
		case 2:
			sql = 	"SELECT id, nombreGrupo " +
					" FROM grupos " +
					" WHERE nombreGrupo LIKE '%" + busqueda + "%' " +	
					" LIMIT 0,20";
			break;
		}
		
		//System.out.println(sql);
		ResultSet rs = s.executeQuery(sql);
		DefaultListModel listModel = new DefaultListModel();
		
		grupoId.clear();
		int i = 0;
		
		while(rs.next()){
			grupo = new Grupo(rs.getString("nombreGrupo"), rs.getInt("id"));
			grupoId.put(i, grupo);
			listModel.addElement(grupo.getNombre());
			i++;
		}
		
		rs.close ();
		conn.close();
		return listModel;
	}

	
	public static String getTemasDisco(Disco disco) throws SQLException {
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		String sql = "";
		
		sql =	"SELECT url " +
				" FROM temas " +
				" WHERE idDisco = '" + disco.getId() + "';";
		
		ResultSet rs = s.executeQuery(sql);
		
		String urls = "";
		
		while(rs.next())
			urls += " \"" + rs.getString("url") + "\" ";
		
		
		rs.close ();
		conn.close();
		return urls;
	}


	public static String getTemasDisco(Grupo grupo) throws SQLException {
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		String sql = "";
		
		sql =	"SELECT url " +
				" FROM temas " +
				" WHERE idGrupo = '" + grupo.getId() + "';";
		
		ResultSet rs = s.executeQuery(sql);
		
		String urls = "";
		
		while(rs.next())
			urls += " \"" + rs.getString("url") + "\" ";
		
		
		rs.close ();
		conn.close();
		return urls;
		
	}

	public static String getNombreReproductor() throws SQLException {
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		String sql = "";
		
		sql =	"SELECT nombre " +
				" FROM reproductor " +
				" WHERE id = 1;";
		
		ResultSet rs = s.executeQuery(sql);

		//System.out.println(sql);
		String nombre = null;
		
		if (rs.next())
			nombre = rs.getString("nombre");
		
		rs.close ();
		conn.close();
		return nombre;
	}

	public static void crearTablaReproductor() throws SQLException {
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();
		
		
		statement.execute("DROP TABLE IF EXISTS `reproductor`");
		String sql =" CREATE TABLE reproductor ( " +
					" `id` INT NOT NULL , " +
					" `nombre` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) " +
					" )  ; ";
		
		//System.out.println(sql);
		statement.executeUpdate(sql);
		conexion.close();
	}

	public static void setReproductorSeleccionado(String reproductor) throws SQLException {
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();

		String sql ="INSERT INTO reproductor ( " +
					"	`id` , `nombre` " +
					" ) " +
					" VALUES ( 1 , '" + reproductor + "' );";
		
		//System.out.println(sql);
		statement.executeUpdate(sql);
		conexion.close();
	}

	public static void quitarViejoReproductor() throws SQLException {
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();

		String sql ="DELETE FROM reproductor WHERE id=1;";
		
		//System.out.println(sql);
		statement.executeUpdate(sql);
		
		sql ="DELETE FROM otroReproductor WHERE id=1;";
		try {
			statement.executeUpdate(sql);
		}
		catch (SQLException e) {
			//e.printStackTrace();
		}
		conexion.close();		
	}

	public static void crearTablaOtroReproductor() throws SQLException {
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();
		
		statement.execute("DROP TABLE IF EXISTS `otroReproductor`");
		String sql =" CREATE TABLE otroReproductor ( " +
					" `id` INT NOT NULL , " +
					" `textoReproductor` VARCHAR( 100 ) NOT NULL , " +
					" `textoEncolar` VARCHAR( 100 ) NOT NULL , " +
					" `textoEncolarReproducir` VARCHAR( 100 ) NOT NULL , " +
					" `textoEncolarLista` VARCHAR( 100 ) NOT NULL , " +
					" `textoEncolarReproducirLista` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) " +
					" )  ; ";
		
		//System.out.println(sql);
		statement.executeUpdate(sql);
		conexion.close();
	}

	public static void cargarDatosOtroReproductor(String textoEncolar,String textoEncolarLista, String textoEncolarReproducir, String textoEncolarReproducirLista, String textoReproductor) throws SQLException {
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();

		String sql ="INSERT INTO otroReproductor ( " +
					"	`id` , `textoReproductor`, `textoEncolar`, `textoEncolarReproducir`, `textoEncolarLista`, `textoEncolarReproducirLista` " +
					" ) " +
					" VALUES ( 1 , '" + textoReproductor + "' , '" + textoEncolar + "' , '" + textoEncolarReproducir + "' , '" + textoEncolarLista + "' , '" + textoEncolarReproducirLista + "' );";
		
		//System.out.println(sql);
		statement.executeUpdate(sql);
		conexion.close();
	}

	public static String[] getComandosOtroReproductor() throws SQLException {
		Connection conn = PrincipalDAO.connect();
		Statement s = conn.createStatement();
		String sql = "";

		String[] comandos = new String[]{"","","","",""};
		
		sql =	"SELECT * " +
				" FROM otroReproductor " +
				" WHERE id = 1;";
		
		ResultSet rs = s.executeQuery(sql);

		//System.out.println(sql);
		if (rs.next()) {
			comandos[0] = rs.getString("textoReproductor");
			comandos[1] = rs.getString("textoEncolar");
			comandos[2] = rs.getString("textoEncolarReproducir");
			comandos[3] = rs.getString("textoEncolarLista");
			comandos[4] = rs.getString("textoEncolarReproducirLista");
		}
		
		rs.close ();
		conn.close();
		return comandos;
	}
}
